import logging
from typing import Optional, List
from fastapi import APIRouter, Depends, Query, Path
from pydantic import BaseModel, Field
from sqlalchemy import text
from sqlalchemy.engine import create
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

contract_head_router = APIRouter(prefix="/fault/contractHead", tags=["维护合约管理"],
                                 dependencies=[Depends(oauth2_scheme)])


class ContractSearch(PaginationBase):
    # 筛选条件
    fsiteNo: Optional[str] = Field("", description="站点编号")
    ftakedateStart: Optional[str] = Field("", description="开始日期(yyyyMMdd)")
    ftakedateEnd: Optional[str] = Field("", description="结束日期(yyyyMMdd)")
    fbillno: Optional[str] = Field("", description="合约单号（支持前缀匹配）")
    frepairuser: Optional[str] = Field("", description="维修人员")
    fbillstate: Optional[str] = Field("", description="合约状态编码")


@contract_head_router.get("/list", summary="维护合约查询接口")
def list_contract(contract: ContractSearch = Query(), db: Session = Depends(get_db)):
    try:
        with db.begin():
            param = {}
            sql = "select count(1) from lims.s_tc_contract_head where 1=1"
            if contract.fsiteNo != "":
                sql = sql + " and fsite_no = :site_no "
                param["site_no"] = contract.fsiteNo
            if contract.fbillno != "":
                sql = sql + " and fbillno like concat('%', :fbillno ,'%')"
                param["fbillno"] = contract.fbillno
            if contract.frepairuser != "":
                sql = sql + " and frepairuser = :frepairuser "
                param["frepairuser"] = contract.frepairuser
            if contract.fbillstate != "":
                sql = sql + " and fbillstate = :fbillstate "
                param["fbillstate"] = contract.fbillstate
            if (contract.ftakedateStart != "") and (contract.ftakedateEnd != ""):
                sql = sql + " and to_char(fmmdate,'yyyymmdd') between :ftakedateStart  and :ftakedateEnd "
                param["ftakedateStart"] = contract.ftakedateStart
                param["ftakedateEnd"] = contract.ftakedateEnd
            count = db.execute(text(sql), param).scalar()

            param = {}
            sql = "select fbillno,fsite_no,fmmdate,fsupno,frepairuser,fbegdate,fenddate,fitemcount,"
            sql = sql + " ftotalamt,fupddate,foperator,fcheckdate,fcheckuser,fbillstate"
            sql = sql + " from lims.s_tc_contract_head where 1=1"
            if contract.fsiteNo != "":
                sql = sql + " and fsite_no = :site_no "
                param["site_no"] = contract.fsiteNo
            if contract.fbillno != "":
                sql = sql + " and fbillno like concat('%', :fbillno ,'%')"
                param["fbillno"] = contract.fbillno
            if contract.frepairuser != "":
                sql = sql + " and frepairuser = :frepairuser "
                param["frepairuser"] = contract.frepairuser
            if contract.fbillstate != "":
                sql = sql + " and fbillstate = :fbillstate "
                param["fbillstate"] = contract.fbillstate
            if (contract.ftakedateStart != "") and (contract.ftakedateEnd != ""):
                sql = sql + " and to_char(fmmdate,'yyyymmdd') between :ftakedateStart  and :ftakedateEnd "
                param["ftakedateStart"] = contract.ftakedateStart
                param["ftakedateEnd"] = contract.ftakedateEnd
            if contract.order == "desc":
                sql = sql + " order by  fmmdate,fbillno desc "
            else:
                sql = sql + " order by  fmmdate,fbillno "
            sql = sql + " limit :limit offset :offset "
            param["offset"] = contract.offset
            param["limit"] = contract.limit
            data = db.execute(text(sql), param).fetchall()
            # details 明细列表
            param = {}
            sql_3 = " select b.fbillno,b.fline,b.fpoint_size,b.fequipmentbarcode,b.feuipmentid,b.fbuyprice,b.fmmprice"
            sql_3 = sql_3 + " from lims.s_tc_contract_head a,lims.s_tc_contract_detail b where a.fbillno=b.fbillno "
            if contract.fsiteNo != "":
                sql_3 = sql_3 + " and fsite_no = :site_no "
                param["site_no"] = contract.fsiteNo
            if contract.fbillno != "":
                sql_3 = sql_3 + " and a.fbillno like concat('%', :fbillno ,'%') "
                param["fbillno"] = contract.fbillno
            if contract.frepairuser != "":
                sql_3 = sql_3 + " and frepairuser = :frepairuser "
                param["frepairuser"] = contract.frepairuser
            if contract.fbillstate != "":
                sql_3 = sql_3 + " and fbillstate = :fbillstate "
                param["fbillstate"] = contract.fbillstate
            if (contract.ftakedateStart != "") and (contract.ftakedateEnd != ""):
                sql_3 = sql_3 + " and to_char(fmmdate,'yyyymmdd') between :ftakedateStart  and :ftakedateEnd "
                param["ftakedateStart"] = contract.ftakedateStart
                param["ftakedateEnd"] = contract.ftakedateEnd
            sql_3 = sql_3 + " order by b.fbillno,b.fline"
            data_detail = db.execute(text(sql_3), param).fetchall()

            data_list = []
            for key in range(len(data)):
                d1 = {}
                d1['fbillno'] = data[key][0]
                d1['fsiteno'] = data[key][1]
                d1['fmmdate'] = data[key][2]
                d1['fsupno'] = data[key][3]
                d1['frepairuser'] = data[key][4]
                d1['fbegdate'] = data[key][5]
                d1['fenddate'] = data[key][6]
                d1['fitemcount'] = data[key][7]
                d1['ftotalamt'] = data[key][8]
                d1['fupddate'] = data[key][9]
                d1['foperator'] = data[key][10]
                d1['fcheckdate'] = data[key][11]
                d1['fcheckuser'] = data[key][12]
                d1['fbillstate'] = data[key][13]
                sbillno = data[key][0]

                detail_list = []
                for j1 in range(len(data_detail)):
                    if (str(data_detail[j1][0]) == str(sbillno)):
                        d2 = {}
                        d2['fbillno'] = data_detail[j1][0]
                        d2['fline'] = data_detail[j1][1]
                        d2['fpointsize'] = data_detail[j1][2]
                        d2['fequipmentbarcode'] = data_detail[j1][3]
                        d2['feuipmentid'] = data_detail[j1][4]
                        d2['fbuyprice'] = data_detail[j1][5]
                        d2['fmmprice'] = data_detail[j1][6]
                        detail_list.append(d2)

                d1["details"] = detail_list
                data_list.append(d1)

            info_dict = {"list": data_list, "limit": contract.limit, "page": contract.pageIndex,
                         "pages": (count + contract.limit - 1) // contract.limit,
                         "total": count}
            return BaseResponse.success(data=info_dict)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


class ContractDetail(BaseModel):
    """合约明细项请求参数"""
    fline: int = Field(..., description="明细行号")
    fpointSize: str = Field(..., description="点位尺寸")
    fequipmentbarcode: str = Field(..., description="设备条码")
    feuipmentid: int = Field(..., description="设备ID")
    fbuyprice: float = Field(..., description="购买价格")
    fmmprice: float = Field(..., description="维护价格")


class ContractCreate(BaseModel):
    """维护合约新增请求参数"""
    fbillno: str = Field(..., description="合约单号（唯一标识）")
    fbegdate: str = Field(..., description="合约开始日期")
    fbillstate: int = Field(..., description="合约状态编码，默认1")
    fenddate: str = Field(..., description="合约结束日期")
    fmmdate: str = Field(..., description="合约日期")
    fsiteNo: str = Field(..., description="站点编号")
    fsupno: str = Field(..., description="供应商编号")
    frepairuser: str = Field(..., description="维修人员")
    foperator: str = Field(..., description="操作人")
    fupddate: str = Field(..., description="更新日期")
    details: List[ContractDetail] = Field(..., description="合约明细列表")


@contract_head_router.post("/create", summary="维护合约新增接口")
def create_contract(create: ContractCreate, db: Session = Depends(get_db)):
    try:
        fitemcount = 0
        ftotalamt = 0
        fbillno = create.fbillno
        # 返回内容
        d1 = {}
        with db.begin():
            detail_list = []
            jsondata = create.details
            for data in jsondata:
                sql = "insert into lims.s_tc_contract_detail(fbillno,fline,fpoint_size,fequipmentbarcode,feuipmentid,fbuyprice,fmmprice)"
                sql = sql + " values( :fbillno , :fline , :fpointSize , :fequipmentbarcode , :feuipmentid , :fbuyprice , :fmmprice )"
                db.execute(text(sql), {
                    "fbillno": fbillno,
                    "fline": data.fline,
                    "fpointsize": data.fpointSize,
                    "fequipmentbarcode": data.fequipmentbarcode,
                    "feuipmentid": data.feuipmentid,
                    "fbuyprice": data.fbuyprice,
                    "fmmprice": data.fmmprice
                })
                fitemcount = fitemcount + 1
                ftotalamt = ftotalamt + float(data.fmmprice)
                # 返回明细
                d2 = {}
                d2['fbillno'] = fbillno
                d2['fline'] = data.fline
                d2['fbuyprice'] = data.fbuyprice
                d2['fequipmentbarcode'] = data.fequipmentbarcode
                d2['feuipmentid'] = float(data.feuipmentid)
                d2['fmmprice'] = float(data.fmmprice)
                d2['fpointSize'] = float(data.fpointSize)
                detail_list.append(d2)
            sql = "insert into lims.s_tc_contract_head(fbillno,fsite_no,fmmdate,fsupno,frepairuser,fbegdate,fenddate,fitemcount,ftotalamt,fupddate,foperator,fbillstate)"
            sql = sql + " values( :fbillno , :fsiteNo , :fmmdate , :fsupno , :frepairuser , :fbegdate , :fenddate , :fitemcount , :ftotalamt , :fupddate , :foperator ,1)"
            db.execute(text(sql), {
                "fbillno": fbillno,
                "fsiteNo": create.fsiteNo,
                "fmmdate": create.fmmdate,
                "fsupno": create.fsupno,
                "frepairuser": create.frepairuser,
                "fbegdate": create.fbegdate,
                "fenddate": create.fenddate,
                "fitemcount": fitemcount,
                "ftotalamt": ftotalamt,
                "fupddate": create.fupddate,
                "foperator": create.foperator,
            })
            # 返回
            d1['fbillno'] = fbillno
            d1['fbegdate'] = create.fbegdate
            d1['fbillstate'] = create.fbillstate
            d1['fcheckdate'] = None
            d1['fcheckuser'] = None
            d1['fenddate'] = create.fenddate
            d1['fmmdate'] = create.fmmdate
            d1['fitemcount'] = fitemcount
            d1['ftotalamt'] = float(ftotalamt)
            d1['frepairuser'] = create.frepairuser
            d1['fsiteNo'] = create.fsiteNo
            d1['fsupno'] = create.fsupno
            d1['fupddate'] = create.fupddate
            d1['ftakedateEnd'] = None
            d1['ftakedateStart'] = None
            d1['fbillstate'] = create.fbillstate
            d1["details"] = detail_list
            return BaseResponse.success(data=d1)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@contract_head_router.post("/update", summary="维护合约修改接口")
def update_contract(update: ContractCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        fitemcount = 0
        ftotalamt = 0
        fbillno = update.fbillno
        fbillstate = update.fbillstate
        userid = EncryptionMiddleware.get_current_user(token)
        with db.begin():
            if (fbillstate == 8):
                sql = "update lims.s_tc_contract_head "
                sql = sql + " set fbillstate=8,fcheckdate=now(),fcheckuser= :userid "
                sql = sql + " where fbillno= :fbillno "
                db.execute(text(sql), {
                    "fbillno": fbillno,
                    "userid": userid,
                })
            else:
                sql = "delete from lims.s_tc_contract_detail where fbillno= :fbillno "
                db.execute(text(sql), {
                    "fbillno": fbillno,
                })

                jsondata = update.details
                for data in jsondata:
                    sql = "insert into lims.s_tc_contract_detail(fbillno,fline,fpoint_size,fequipmentbarcode,feuipmentid,fbuyprice,fmmprice)"
                    sql = sql + " values( :fbillno , :fline , :fpointSize , :fequipmentbarcode , :feuipmentid , :fbuyprice , :fmmprice )"
                    db.execute(text(sql), {
                        "fbillno": fbillno,
                        "fline": data.fline,
                        "fpointSize": data.fpointSize,
                        "fequipmentbarcode": data.fequipmentbarcode,
                        "feuipmentid": data.feuipmentid,
                        "fbuyprice": data.fbuyprice,
                        "fmmprice": data.fmmprice,
                    })
                    fitemcount = float(fitemcount) + 1
                    ftotalamt = float(ftotalamt) + float(data['fmmprice'])
                sql = "update lims.s_tc_contract_head "
                sql = sql + " set fmmdate= :fmmdate ,fsupno= :fsupno ,frepairuser= :frepairuser "
                sql = sql + ",fbegdate= :fbegdate ,fenddate= :fenddate "
                sql = sql + ",fitemcount= :fitemcount ,ftotalamt= :ftotalamt "
                sql = sql + ",fupddate= :fupddate ,foperator= :foperator "
                sql = sql + " where fbillno= :fbillno "
                db.execute(text(sql), {
                    "fmmdate": update.fmmdate,
                    "fsupno": update.fsupno,
                    "frepairuser": update.frepairuser,
                    "fbegdate": update.fbegdate,
                    "fenddate": update.fenddate,
                    "fitemcount": fitemcount,
                    "ftotalamt": ftotalamt,
                    "fupddate": update.fupddate,
                    "foperator": update.foperator,
                    "fbillno": fbillno,
                })
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")
